package dbConnection;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;

import users.Employer;
import users.Student;

public class DbManage
{
    public Connection openConnection()
    {
	Connection conn = null;
	String url = "jdbc:mysql://localhost:3306/";
	String dbName = "sst";
	String driver = "com.mysql.jdbc.Driver";
	String userName = "root";
	String password = "1234";
	try
	{
	    Class.forName(driver).newInstance();
	    conn = DriverManager.getConnection(url + dbName, userName, password);
	}
	catch (Exception e)
	{
	    // TODO Auto-generated catch block
	}
	return conn;
    }
    public String addNewEmployer(Employer employer)
    {
	try
	{
	    Connection conn = openConnection();
	    Statement statement = conn.createStatement();
	    statement.executeUpdate("insert into employer(Name,Field,Address,Email,UserName,Password) values ('"
		    + employer.getName() + "','" + employer.getField() + "','" + employer.getAddress() + "','"
		    + employer.getEmail() + "','" + employer.getUsername() + "','" + employer.getPassword() + "')");
	    conn.close();
	    statement.close();
	}
	catch (Exception e)
	{
	    e.printStackTrace();
	}
	return "MainPage.xhtml";
    }
    public String addNewStudent(Student student)
    {
	try
	{
	    Connection conn = openConnection();
	    Statement statement = conn.createStatement();
	    statement
		    .executeUpdate("insert into student(Name,Surname,University,Department,Gender,Email,Gpa,Username,Password) "
			    + "values ('"
			    + student.getName()
			    + "','"
			    + student.getSurname()
			    + "','"
			    + student.getUniversity()
			    + "','"
			    + student.getDepartment()
			    + "','"
			    + student.getGender()
			    + "','"
			    + student.getEmail()
			    + "','"
			    + student.getGpa()
			    + "','"
			    + student.getUsername()
			    + "','" + student.getPassword() + "')");
	    conn.close();
	    statement.close();
	}
	catch (Exception e)
	{
	}
	return "MainPage.xhtml";
    }
    public boolean isRegisteredStudent(String username, String password)
    {
	int count = 0;
	try
	{
	    Connection conn = openConnection();
	    Statement statement = conn.createStatement();
	    ResultSet result = statement.executeQuery("select count(*) from student where Username='" + username
		    + "' and Password=" + password);
	    result.next();
	    count = result.getInt(1);
	    conn.close();
	    result.close();
	    statement.close();
	}
	catch (Exception e)
	{
	    e.printStackTrace();
	}
	if (count>0)
	{
	    return true;
	}
	else
	{
	    return false;
	}
    }
    public boolean isRegisteredEmployer(String username, String password)
    {
	int count = 0;
	try
	{
	    Connection conn = openConnection();
	    Statement statement = conn.createStatement();
	    ResultSet result = statement.executeQuery("select count(*) from employer where Username='" + username
		    + "' and Password=" + password);
	    result.next();
	    count = result.getInt(1);
	    conn.close();
	    result.close();
	    statement.close();
	}
	catch (Exception e)
	{
	    e.printStackTrace();
	}
	if (count>0)
	{
	    return true;
	}
	else
	{
	    return false;
	}
    }
}
